﻿Imports System.Data.SqlClient

Public Class frmCuentasUna
    Dim mdsDatos As New DataSet
    Dim total As Double
    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)


    End Sub

    Private Sub frmCuentasUna_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        CargaCuentas()

    End Sub
    Sub CargaCuentas()
        lstCuentas1.Items.Clear()

        Dim cnconn As New SqlConnection
        cnconn.ConnectionString = CitraConnection
        Dim strSql As String = "SELECT Nombre FROM Adeudos WHERE Aplicacion=0 AND IdAdeudo> 4 ORDER BY Nombre"

        cnconn.Open()
        Dim cmdBuscar As New SqlCommand
        cmdBuscar.Connection = cnconn
        Dim cmdAsignar As New SqlCommand
        cmdAsignar.Connection = cnconn

        cmdBuscar.CommandText = strSql
        Dim rdBuscar As SqlDataReader
        rdBuscar = cmdBuscar.ExecuteReader

        Do While rdBuscar.Read()
            lstCuentas1.Items.Add(rdBuscar("Nombre").ToString.Trim)
        Loop
    End Sub

    Private Sub lstCuentas_KeyPress(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles lstCuentas.KeyPress
        If e.KeyChar = Microsoft.VisualBasic.ChrW(13) Then
            Button2_Click(Me, System.EventArgs.Empty)
        End If
    End Sub

    Private Sub lstCuentas_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles lstCuentas.SelectedIndexChanged

    End Sub
  
    Sub imprime()
        Dim xl As Object
        Dim wb As Object
        xl = CreateObject("Excel.Application")
        xl.Visible = True
        xl.Workbooks.Add()
        wb = xl.activeworkbook
        Dim rng As Integer = 5

        wb.SHEETS(1).cells(1, 1).value = "Imprimiendo..."
        wb.SHEETS(1).columns(1).columnwidth = 15

        Dim cnConn As New SqlConnection
        cnConn.ConnectionString = CitraConnection

        Dim strSql As String = ""

        strSql = "SELECT M.IdAdeudo,Nombre,Fecha,Monto,Tipo FROM Movimientos M INNER JOIN ADEUDOS A ON M.IdAdeudo=A.IdAdeudo WHERE  IdEmpleado=" & frmModuloEmpleadosDetalle.txtIDEmpleado.Text & " AND Nombre='" & lstCuentas.Text.Trim & "' ORDER BY M.IdAdeudo,Fecha"

        Dim cmdBuscar As New SqlCommand
        cmdBuscar.Connection = cnConn
        cmdBuscar.CommandText = strSql
        Dim rdBuscar As SqlDataReader
        cnConn.Open()
        rdBuscar = cmdBuscar.ExecuteReader

        Dim Saldo As Double = 0
        Dim Cargos As Double = 0
        Dim Abonos As Double = 0
        Dim TotalCargos As Double = 0
        Dim TotalAbonos As Double = 0
        Dim SaldoTotal As Double = 0
        Dim IdAdeudo As Integer = 0
        Dim Primero As Boolean = True
        Dim tipo As Integer

        rng = rng + 1
        wb.SHEETS(1).cells(rng, 1).value = "Fecha"
        wb.SHEETS(1).cells(rng, 2).value = "Concepto"
        wb.SHEETS(1).cells(rng, 3).value = "Cargos"
        wb.SHEETS(1).cells(rng, 4).value = "Abonos"
        wb.SHEETS(1).cells(rng, 5).value = "Saldo"
        For i = 1 To 5
            wb.SHEETS(1).cells(rng, i).font.bold = True
        Next
        rng = rng + 1

        Do While rdBuscar.Read()
            tipo = rdBuscar("Tipo")
            If rdBuscar("Monto") > 0 Then
                wb.SHEETS(1).cells(rng, 1).value = rdBuscar("fecha")
                If tipo = 0 Then
                    wb.SHEETS(1).cells(rng, 2).value = "Cargo de " & rdBuscar("NOMBRE").ToString.Trim
                    wb.SHEETS(1).cells(rng, 3).value = rdBuscar("MONTO")
                    wb.SHEETS(1).cells(rng, 4).value = 0
                    Cargos = Cargos + rdBuscar("MONTO")
                    TotalCargos = TotalCargos + rdBuscar("MONTO")
                    Saldo = Saldo + rdBuscar("monto")
                    SaldoTotal = SaldoTotal + rdBuscar("monto")
                Else
                    wb.SHEETS(1).cells(rng, 2).value = "Abono de " & rdBuscar("NOMBRE").ToString.Trim
                    wb.SHEETS(1).cells(rng, 3).value = 0
                    wb.SHEETS(1).cells(rng, 4).value = rdBuscar("MONTO")
                    Abonos = Abonos + rdBuscar("MONTO")
                    TotalAbonos = TotalAbonos + rdBuscar("MONTO")
                    Saldo = Saldo - rdBuscar("monto")
                    SaldoTotal = SaldoTotal - rdBuscar("monto")
                End If

                wb.SHEETS(1).cells(rng, 5).value = Saldo
                rng = rng + 1
            End If
        Loop

        wb.SHEETS(1).cells(rng, 2).value = "Totales"
        wb.SHEETS(1).cells(rng, 3).value = Cargos
        wb.SHEETS(1).cells(rng, 4).value = Abonos
        wb.SHEETS(1).cells(rng, 5).value = Saldo
        For i = 2 To 5
            wb.SHEETS(1).cells(rng, i).font.bold = True
            wb.SHEETS(1).cells(rng, i).font.italic = True
        Next

        wb.SHEETS(1).columns(3).numberformat = "#,##0.00"
        wb.SHEETS(1).columns(4).numberformat = "#,##0.00"
        wb.SHEETS(1).columns(5).numberformat = "#,##0.00"

        wb.SHEETS(1).COLUMNS(1).ColumnWidth = 10
        wb.SHEETS(1).COLUMNS(2).ColumnWidth = 25
        wb.SHEETS(1).COLUMNS(3).ColumnWidth = 15
        wb.SHEETS(1).COLUMNS(4).ColumnWidth = 15
        wb.SHEETS(1).COLUMNS(5).ColumnWidth = 15

        wb.SHEETS(1).cells(1, 1).value = EmpresaActiva
        wb.SHEETS(1).cells(1, 1).FONT.SIZE = 14
        wb.SHEETS(1).cells(2, 1).value = "Estado de cuenta de " & frmModuloEmpleadosDetalle.txtNombre.Text & " " & frmModuloEmpleadosDetalle.txtPaterno.Text & " " & frmModuloEmpleadosDetalle.txtMaterno.Text
        wb.SHEETS(1).cells(2, 1).FONT.SIZE = 10
        wb.SHEETS(1).cells(3, 1).value = "Todos los adeudos"
        wb.SHEETS(1).cells(3, 1).FONT.SIZE = 10
        wb.SHEETS(1).cells(4, 1).value = "Impreso por: " & usuarioactivo & " el " & Now().Day & "-" & Now().Month & "-" & Now().Year
        wb.SHEETS(1).cells(4, 1).FONT.SIZE = 8

    End Sub
   
    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)

    End Sub

    Private Sub btnAbrir_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAbrir.Click
        imprime()
        Me.Close()
    End Sub

    Private Sub RadButton2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles RadButton2.Click
        Me.Close()
    End Sub
End Class